package com.pokermind.repository.dao.impl;

import com.pokermind.model.po.PokerHand;
import com.pokermind.repository.dao.PokerHandDao;
import com.pokermind.repository.mapper.PokerHandRowMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.List;
import java.util.Optional;

/**
 * 扑克手牌DAO实现类 对应 poker_hands 表
 *
 * @author PokerMind Team
 */
@Repository
public class PokerHandDaoImpl implements PokerHandDao {

    private static final Logger logger = LoggerFactory.getLogger(PokerHandDaoImpl.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private final PokerHandRowMapper rowMapper = new PokerHandRowMapper();

    @Override
    public PokerHand save(PokerHand hand) {
        String sql = """
                INSERT INTO poker_hands (
                    hand_id, table_id, user_id,
                    button_seat, small_blind, big_blind, ante, players_dealt,
                    current_street, board_cards, current_pot, status,
                    final_pot, rake_amount,
                    run_it_twice, run_twice_details,
                    create_time, update_time, completed_time, players_info
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """;

        KeyHolder keyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            int idx = 1;

            // 基础信息
            ps.setString(idx++, hand.getHandId());
            ps.setString(idx++, hand.getTableId());
            ps.setLong(idx++, hand.getUserId());

            // 牌局配置
            ps.setInt(idx++, hand.getButtonSeat());
            ps.setBigDecimal(idx++, hand.getSmallBlind());
            ps.setBigDecimal(idx++, hand.getBigBlind());
            ps.setBigDecimal(idx++, hand.getAnte() != null ? hand.getAnte() : BigDecimal.ZERO);
            ps.setInt(idx++, hand.getPlayersDealt());

            // 牌局状态
            ps.setString(idx++, hand.getCurrentStreet());
            ps.setString(idx++, hand.getBoardCards());
            ps.setBigDecimal(idx++, hand.getCurrentPot());
            ps.setString(idx++, hand.getStatus());

            // 结果信息
            ps.setBigDecimal(idx++, hand.getFinalPot());
            ps.setBigDecimal(idx++, hand.getRakeAmount());

            // ✅ V4.5新增：Run It Twice字段
            ps.setObject(idx++, hand.getRunItTwice()); // Boolean，可以为null
            ps.setString(idx++, hand.getRunTwiceDetails()); // JSON，可以为null

            // 时间信息
            ps.setLong(idx++, hand.getCreateTime() != null ? hand.getCreateTime() : System.currentTimeMillis());
            ps.setLong(idx++, hand.getUpdateTime() != null ? hand.getUpdateTime() : System.currentTimeMillis());
            ps.setObject(idx++, hand.getCompletedTime()); // 可以为null

            // 玩家信息JSON
            ps.setString(idx++, hand.getPlayersInfo()); // 完整玩家信息JSON

            return ps;
        }, keyHolder);

        Number generatedId = keyHolder.getKey();
        if (generatedId != null) {
            hand.setId(generatedId.longValue());
        }
        return hand;
    }

    @Override
    public int update(PokerHand hand) {
        String sql = """
                UPDATE poker_hands SET 
                    hand_id = ?, table_id = ?, user_id = ?, 
                    button_seat = ?, small_blind = ?, big_blind = ?, 
                    ante = ?, players_dealt = ?, current_street = ?, board_cards = ?, current_pot = ?, status = ?,
                    final_pot = ?, rake_amount = ?, final_results = ?,
                    run_it_twice = ?, run_twice_details = ?,
                    update_time = ?, completed_time = ?, players_info = ?
                WHERE id = ?
            """;

        return jdbcTemplate.update(sql, hand.getHandId(), hand.getTableId(), hand.getUserId(),
            hand.getButtonSeat(), hand.getSmallBlind(), hand.getBigBlind(), hand.getAnte(), hand.getPlayersDealt(), 
            hand.getCurrentStreet(), hand.getBoardCards(), hand.getCurrentPot(), hand.getStatus(), 
            hand.getFinalPot(), hand.getRakeAmount(), hand.getFinalResults(),
            hand.getRunItTwice(), hand.getRunTwiceDetails(), // ✅ V4.5新增
            System.currentTimeMillis(), // update_time - 总是更新为当前时间
            hand.getCompletedTime(), // completed_time - 可以为null
            hand.getPlayersInfo(), // players_info - 完整玩家信息JSON
            hand.getId());
    }

    @Override
    public Optional<PokerHand> findById(Long id) {
        String sql = "SELECT * FROM poker_hands WHERE id = ?";
        try {
            PokerHand hand = jdbcTemplate.queryForObject(sql, rowMapper, id);
            return Optional.ofNullable(hand);
        } catch (Exception e) {
            return Optional.empty();
        }
    }

    @Override
    public Optional<PokerHand> findByHandId(String handId) {
        String sql = "SELECT * FROM poker_hands WHERE hand_id = ?";
        try {
            PokerHand hand = jdbcTemplate.queryForObject(sql, rowMapper, handId);
            return Optional.ofNullable(hand);
        } catch (Exception e) {
            return Optional.empty();
        }
    }

    @Override
    public Optional<PokerHand> findByHandIdAndTableId(String handId, String tableId) {
        String sql = "SELECT * FROM poker_hands WHERE hand_id = ? AND table_id = ?";
        try {
            PokerHand hand = jdbcTemplate.queryForObject(sql, rowMapper, handId, tableId);
            return Optional.ofNullable(hand);
        } catch (Exception e) {
            return Optional.empty();
        }
    }

    @Override
    public Optional<PokerHand> findByHandIdAndUserId(String handId, Long userId) {
        String sql = "SELECT * FROM poker_hands WHERE hand_id = ? AND user_id = ?";
        try {
            PokerHand hand = jdbcTemplate.queryForObject(sql, rowMapper, handId, userId);
            return Optional.ofNullable(hand);
        } catch (Exception e) {
            return Optional.empty();
        }
    }

    @Override
    public boolean existsByHandId(String handId) {
        String sql = "SELECT COUNT(*) FROM poker_hands WHERE hand_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, handId);
        return count != null && count > 0;
    }

    @Override
    public boolean existsByHandIdAndTableId(String handId, String tableId) {
        String sql = "SELECT COUNT(*) FROM poker_hands WHERE hand_id = ? AND table_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, handId, tableId);
        return count != null && count > 0;
    }

    @Override
    public boolean existsByHandIdAndUserId(String handId, Long userId) {
        String sql = "SELECT COUNT(*) FROM poker_hands WHERE hand_id = ? AND user_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, handId, userId);
        return count != null && count > 0;
    }

    @Override
    public List<PokerHand> findByUserId(Long userId, int limit) {
        String sql = "SELECT * FROM poker_hands WHERE user_id = ? ORDER BY create_time DESC LIMIT ?";
        return jdbcTemplate.query(sql, rowMapper, userId, limit);
    }

    @Override
    public List<PokerHand> findByUserIdAndTimeRange(Long userId, Long startTime, Long endTime) {
        String sql =
            "SELECT * FROM poker_hands WHERE user_id = ? AND create_time BETWEEN ? AND ? ORDER BY create_time DESC";
        return jdbcTemplate.query(sql, rowMapper, userId, startTime, endTime);
    }

    @Override
    public List<PokerHand> findIncompleteHands(Long userId) {
        String sql = userId != null
            ? "SELECT * FROM poker_hands WHERE user_id = ? AND status = 'active' ORDER BY create_time DESC"
            : "SELECT * FROM poker_hands WHERE status = 'active' ORDER BY create_time DESC";

        if (userId != null) {
            return jdbcTemplate.query(sql, rowMapper, userId);
        } else {
            return jdbcTemplate.query(sql, rowMapper);
        }
    }

    @Override
    public int deleteById(Long id) {
        String sql = "DELETE FROM poker_hands WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }

    @Override
    public long countByUserId(Long userId) {
        String sql = "SELECT COUNT(*) FROM poker_hands WHERE user_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, userId);
        return count != null ? count.longValue() : 0L;
    }

    @Override
    @Deprecated
    public List<PokerHand> findByUserIdAndPosition(Long userId, String position, int limit) {
        // TODO: Refactor this method - hero_position field no longer exists
        // For now, return all hands for the user (ignoring position filter)
        logger.warn("findByUserIdAndPosition called but hero_position field no longer exists - returning all hands for user");
        String sql = "SELECT * FROM poker_hands WHERE user_id = ? ORDER BY create_time DESC LIMIT ?";
        return jdbcTemplate.query(sql, rowMapper, userId, limit);
    }

    @Override
    @Deprecated
    public int updateHandResult(Long handId, BigDecimal netResult, String finalAction) {
        // TODO: Refactor this method - hero_net_result and hero_final_action fields no longer exist
        // For now, just update the update_time
        logger.warn("updateHandResult called but hero_net_result/hero_final_action fields no longer exist - only updating timestamp");
        String sql = "UPDATE poker_hands SET update_time = ? WHERE id = ?";
        return jdbcTemplate.update(sql, System.currentTimeMillis(), handId);
    }

    @Override
    public long countByUserIdAndTimeRange(Long userId, Long startTime, Long endTime) {
        String sql = "SELECT COUNT(*) FROM poker_hands WHERE user_id = ? AND create_time BETWEEN ? AND ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, userId, startTime, endTime);
        return count != null ? count.longValue() : 0L;
    }

    @Override
    public Optional<PokerHand> findLatestByUserId(Long userId) {
        String sql = "SELECT * FROM poker_hands WHERE user_id = ? ORDER BY create_time DESC LIMIT 1";
        try {
            PokerHand hand = jdbcTemplate.queryForObject(sql, rowMapper, userId);
            return Optional.ofNullable(hand);
        } catch (Exception e) {
            return Optional.empty();
        }
    }

    @Override
    public boolean existsById(Long handId) {
        String sql = "SELECT COUNT(*) FROM poker_hands WHERE id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, handId);
        return count != null && count > 0;
    }
}